﻿using System;
using System.Reflection;
using MDSAdministration.mdsService;
using Microsoft.VisualStudio.TestTools.UnitTesting;
using System.Data;
using ExcelLoader;
using System.Collections.Generic;
using MDSAdministration;
using Constants = MDSAdministration.Constants;

namespace MDSAdministrationTest
{
    [TestClass]
    public class LoadEntityDataTests
    {
        DataSet dsEntityDataA;
        

        [TestMethod]
        public void LoadEntityDataSchemaCompareTest()
        {
            LoadEnityData();
            Common.LoadServer();
            

            EntityData ed = new EntityData();
            DataSet dsServer = ed.GetAttributeDetails("175", Common.ServerA);

            Assert.AreEqual(dsServer.Tables[0].SchemaNameOnlyEquals(dsEntityDataA.Tables[0]), true);
        }

       

        private void LoadEnityData()
        {
            if (dsEntityDataA == null)
            {
                ExcelHelper eh = new ExcelHelper();
                eh.FileName = @"..\..\TestData\EntityData.xlsx";
                eh.SetConnectionString();
                List<SheetDetails> lSheets = eh.GetExcelSheetNames();
                Dictionary<string, DataTable> ds = new Dictionary<string, DataTable>();

                foreach (var sheet in lSheets)
                {
                    DataTable dt = eh.ReadExcelSheet(sheet.SheetName, true);
                    ds.Add(sheet.Name, dt);
                }

                dsEntityDataA = new DataSet();
                dsEntityDataA.Tables.Add(ds["AttributeDetails"]);
            }
        }

        [TestMethod]
        public void LoadEntityDataBuildEntityDataQueryServerATest()
        {
            Common.LoadServer();
            CheckStates cStates = new CheckStates();

            EntityData ed = new EntityData();
            ed.ServerA = Common.ServerA;
            ed.ServerB = Common.ServerB;
            ed.cStates = cStates;

            var ec = new EntityDataCompreDetails();
            ec.EntityMUIDA = "E7D59F8A-570C-435B-B330-2C57A6499E2E";
            ec.EntityMUIDB = "E7D59F8A-570C-435B-B330-2C57A6499E2E";
            var result = ed.BuildEntityDataQueries(ec);
            Assert.AreEqual(result, true);

            string ExpectedResult = "SELECT master.dbo.fn_varbintohexstr(HASHBYTES('MD5',COALESCE(CAST(LTRIM(RTRIM(e.Code)) AS NVARCHAR(4000)),' ')\r\n" 
                + " + COALESCE(CAST(LTRIM(RTRIM(e.Name)) AS NVARCHAR(4000)),' ')\r\n"
                + " + COALESCE(CAST(LTRIM(RTRIM(e.uda_175_3691)) AS NVARCHAR(4000)),' ')\r\n"
                + " + COALESCE(CAST(LTRIM(RTRIM(e.uda_175_3856)) AS NVARCHAR(4000)),' ')\r\n"
                + " + COALESCE(CAST(LTRIM(RTRIM(e.uda_175_3857)) AS NVARCHAR(4000)),' ')\r\n"
                + " + COALESCE(CAST(LTRIM(RTRIM(e.uda_175_3858)) AS NVARCHAR(4000)),' ')\r\n"
                + " + COALESCE(CAST(LTRIM(RTRIM(e.uda_175_3859)) AS NVARCHAR(4000)),' ')\r\n"
                + " + COALESCE(CAST(LTRIM(RTRIM(e.uda_175_3860)) AS NVARCHAR(4000)),' ')\r\n"
                + ")) AS 'servA_HashCode',CAST(1 AS BIT) AS 'IsChecked'\r\n"
                + ",e.Code\r\n"
                + ",e.Code AS 'servA_Code'\r\n"
                + ",e.Name AS 'servA_Name'\r\n"
                + ",e.uda_175_3691 AS 'servA_TestAttribute'\r\n"
                + ",e.uda_175_3856 AS 'servA_DemoAttribute'\r\n"
                + ",e.uda_175_3857 AS 'servA_PSku1'\r\n"
                + ",e.uda_175_3858 AS 'servA_PSku2'\r\n"
                + ",e.uda_175_3859 AS 'servA_TAC1'\r\n" 
                + ",e.uda_175_3860 AS 'servA_TAC2'\r\n"
                + "FROM " + Common.ServerA.Database + ".mdm.tbl_7_175_EN e\r\n"
                + "INNER JOIN " + Common.ServerA.Database + ".mdm.tblModelVersion mv ON e.[Version_ID] = mv.[ID]\r\n"
                + "WHERE e.[Status_ID] = 1 AND mv.[Name] = 'VERSION_1'";

            Assert.AreEqual(ed.queryServerA, ExpectedResult);
        }

        [TestMethod]
        public void LoadEntityDataBuildEntityDataQueryServerBTest()
        {
            Common.LoadServer();
            CheckStates cStates = new CheckStates();

            EntityData ed = new EntityData();
            ed.ServerA = Common.ServerA;
            ed.ServerB = Common.ServerB;
            ed.cStates = cStates;

            var ec = new EntityDataCompreDetails();
            ec.EntityMUIDA = "E7D59F8A-570C-435B-B330-2C57A6499E2E";
            ec.EntityMUIDB = "E7D59F8A-570C-435B-B330-2C57A6499E2E";
            var result = ed.BuildEntityDataQueries(ec);
            Assert.AreEqual(result, true);

            string ExpectedResult = "SELECT master.dbo.fn_varbintohexstr(HASHBYTES('MD5',COALESCE(CAST(LTRIM(RTRIM(e.Code)) AS NVARCHAR(4000)),' ')\r\n" 
                + " + COALESCE(CAST(LTRIM(RTRIM(e.Name)) AS NVARCHAR(4000)),' ')\r\n"
                + " + COALESCE(CAST(LTRIM(RTRIM(e.uda_175_3691)) AS NVARCHAR(4000)),' ')\r\n"
                + " + COALESCE(CAST(LTRIM(RTRIM(e.uda_175_3856)) AS NVARCHAR(4000)),' ')\r\n"
                + " + COALESCE(CAST(LTRIM(RTRIM(e.uda_175_3857)) AS NVARCHAR(4000)),' ')\r\n"
                + " + COALESCE(CAST(LTRIM(RTRIM(e.uda_175_3858)) AS NVARCHAR(4000)),' ')\r\n"
                + " + COALESCE(CAST(LTRIM(RTRIM(e.uda_175_3859)) AS NVARCHAR(4000)),' ')\r\n"
                + " + COALESCE(CAST(LTRIM(RTRIM(e.uda_175_3860)) AS NVARCHAR(4000)),' ')\r\n"
                + ")) AS 'servB_HashCode',CAST(1 AS BIT) AS 'IsChecked'\r\n"
                + ",e.Code\r\n"
                + ",e.Code AS 'servB_Code'\r\n"
                + ",e.Name AS 'servB_Name'\r\n"
                + ",e.uda_175_3691 AS 'servB_TestAttribute'\r\n"
                + ",e.uda_175_3856 AS 'servB_DemoAttribute'\r\n"
                + ",e.uda_175_3857 AS 'servB_PSku1'\r\n"
                + ",e.uda_175_3858 AS 'servB_PSku2'\r\n"
                + ",e.uda_175_3859 AS 'servB_TAC1'\r\n" 
                + ",e.uda_175_3860 AS 'servB_TAC2'\r\n"
                + "FROM " + Common.ServerB.Database + ".mdm.tbl_7_175_EN e\r\n"
                + "INNER JOIN " + Common.ServerB.Database + ".mdm.tblModelVersion mv ON e.[Version_ID] = mv.[ID]\r\n"
                + "WHERE e.[Status_ID] = 1 AND mv.[Name] = 'VERSION_1'";
            
            Assert.AreEqual(ed.queryServerB, ExpectedResult);

        }

        [TestMethod]
        public void LoadEntityDataBuildEntityDataQueryServerACaseInSensitiveTest()
        {
            Common.LoadServer();
            CheckStates cStates = new CheckStates();
            cStates.CheckStatesDefault();
            
            cStates.CompareDataCaseSensitive = false;

            EntityData ed = new EntityData();
            ed.ServerA = Common.ServerA;
            ed.ServerB = Common.ServerB;
            ed.cStates = cStates;

            var ec = new EntityDataCompreDetails();
            ec.EntityMUIDA = "E7D59F8A-570C-435B-B330-2C57A6499E2E";
            ec.EntityMUIDB = "E7D59F8A-570C-435B-B330-2C57A6499E2E";
            var result = ed.BuildEntityDataQueries(ec);
            Assert.AreEqual(result, true);

            string ExpectedResult = "SELECT master.dbo.fn_varbintohexstr(HASHBYTES('MD5',COALESCE(CAST(LTRIM(RTRIM(UPPER(e.Code))) AS NVARCHAR(4000)),' ')\r\n"
                + " + COALESCE(CAST(LTRIM(RTRIM(UPPER(e.Name))) AS NVARCHAR(4000)),' ')\r\n"
                + " + COALESCE(CAST(LTRIM(RTRIM(UPPER(e.uda_175_3691))) AS NVARCHAR(4000)),' ')\r\n"
                + " + COALESCE(CAST(LTRIM(RTRIM(UPPER(e.uda_175_3856))) AS NVARCHAR(4000)),' ')\r\n"
                + " + COALESCE(CAST(LTRIM(RTRIM(UPPER(e.uda_175_3857))) AS NVARCHAR(4000)),' ')\r\n"
                + " + COALESCE(CAST(LTRIM(RTRIM(UPPER(e.uda_175_3858))) AS NVARCHAR(4000)),' ')\r\n"
                + " + COALESCE(CAST(LTRIM(RTRIM(UPPER(e.uda_175_3859))) AS NVARCHAR(4000)),' ')\r\n"
                + " + COALESCE(CAST(LTRIM(RTRIM(UPPER(e.uda_175_3860))) AS NVARCHAR(4000)),' ')\r\n"
                + ")) AS 'servA_HashCode',CAST(1 AS BIT) AS 'IsChecked'\r\n"
                + ",e.Code\r\n"
                + ",e.Code AS 'servA_Code'\r\n"
                + ",e.Name AS 'servA_Name'\r\n"
                + ",e.uda_175_3691 AS 'servA_TestAttribute'\r\n"
                + ",e.uda_175_3856 AS 'servA_DemoAttribute'\r\n"
                + ",e.uda_175_3857 AS 'servA_PSku1'\r\n"
                + ",e.uda_175_3858 AS 'servA_PSku2'\r\n"
                + ",e.uda_175_3859 AS 'servA_TAC1'\r\n"
                + ",e.uda_175_3860 AS 'servA_TAC2'\r\n"
                + "FROM " + Common.ServerA.Database + ".mdm.tbl_7_175_EN e\r\n"
                + "INNER JOIN " + Common.ServerA.Database + ".mdm.tblModelVersion mv ON e.[Version_ID] = mv.[ID]\r\n"
                + "WHERE e.[Status_ID] = 1 AND mv.[Name] = 'VERSION_1'";

            Assert.AreEqual(ed.queryServerA, ExpectedResult);
        }

        [TestMethod]
        public void LoadEntityDataBuildEntityDataQueryServerBCaseInSensitiveTest()
        {
            Common.LoadServer();
            CheckStates cStates = new CheckStates();
            cStates.CheckStatesDefault();
            cStates.CompareDataCaseSensitive = false;

            EntityData ed = new EntityData();
            ed.ServerA = Common.ServerA;
            ed.ServerB = Common.ServerB;
            ed.cStates = cStates;

            var ec = new EntityDataCompreDetails();
            ec.EntityMUIDA = "E7D59F8A-570C-435B-B330-2C57A6499E2E";
            ec.EntityMUIDB = "E7D59F8A-570C-435B-B330-2C57A6499E2E";
            var result = ed.BuildEntityDataQueries(ec);
            Assert.AreEqual(result, true);

            string ExpectedResult = "SELECT master.dbo.fn_varbintohexstr(HASHBYTES('MD5',COALESCE(CAST(LTRIM(RTRIM(UPPER(e.Code))) AS NVARCHAR(4000)),' ')\r\n"
                + " + COALESCE(CAST(LTRIM(RTRIM(UPPER(e.Name))) AS NVARCHAR(4000)),' ')\r\n"
                + " + COALESCE(CAST(LTRIM(RTRIM(UPPER(e.uda_175_3691))) AS NVARCHAR(4000)),' ')\r\n"
                + " + COALESCE(CAST(LTRIM(RTRIM(UPPER(e.uda_175_3856))) AS NVARCHAR(4000)),' ')\r\n"
                + " + COALESCE(CAST(LTRIM(RTRIM(UPPER(e.uda_175_3857))) AS NVARCHAR(4000)),' ')\r\n"
                + " + COALESCE(CAST(LTRIM(RTRIM(UPPER(e.uda_175_3858))) AS NVARCHAR(4000)),' ')\r\n"
                + " + COALESCE(CAST(LTRIM(RTRIM(UPPER(e.uda_175_3859))) AS NVARCHAR(4000)),' ')\r\n"
                + " + COALESCE(CAST(LTRIM(RTRIM(UPPER(e.uda_175_3860))) AS NVARCHAR(4000)),' ')\r\n"
                + ")) AS 'servB_HashCode',CAST(1 AS BIT) AS 'IsChecked'\r\n"
                + ",e.Code\r\n"
                + ",e.Code AS 'servB_Code'\r\n"
                + ",e.Name AS 'servB_Name'\r\n"
                + ",e.uda_175_3691 AS 'servB_TestAttribute'\r\n"
                + ",e.uda_175_3856 AS 'servB_DemoAttribute'\r\n"
                + ",e.uda_175_3857 AS 'servB_PSku1'\r\n"
                + ",e.uda_175_3858 AS 'servB_PSku2'\r\n"
                + ",e.uda_175_3859 AS 'servB_TAC1'\r\n"
                + ",e.uda_175_3860 AS 'servB_TAC2'\r\n"
                + "FROM " + Common.ServerB.Database + ".mdm.tbl_7_175_EN e\r\n"
                + "INNER JOIN " + Common.ServerB.Database + ".mdm.tblModelVersion mv ON e.[Version_ID] = mv.[ID]\r\n"
                + "WHERE e.[Status_ID] = 1 AND mv.[Name] = 'VERSION_1'";

            Assert.AreEqual(ed.queryServerB, ExpectedResult);

        }

        [TestMethod]
        public void LoadEntityDataBuildEntityDetailQueryTest()
        {
            Common.LoadServer();

            Assert.AreEqual(EntityData.buildEntityDetailQuery("E7D59F8A-570C-435B-B330-2C57A6499E2E"), "SELECT e.[ID], e.[EntityTable], e.[Name] FROM mdm.tblEntity e WHERE [e].MUID = 'E7D59F8A-570C-435B-B330-2C57A6499E2E'");
        }

        
    }
}

